package com.swift.authority.common.util;

import com.swift.authority.common.constant.CommonConstant;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.tomcat.util.http.fileupload.FileItem;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.BindException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @author Administrator
 * @description :
 * @date 2022/06/23 20:16
 * @since open jdk 1.8
 */
public class ExcelUtil {
    private static final Logger logger = LoggerFactory.getLogger(ExcelUtil.class);

    public static HttpServletResponse createExcel(HttpServletResponse response) throws IOException {
        //excel第一行标题内容
        String title[] = new String[]{"姓名", "姓名全拼", "性别", "身份证证件类型", "身份证证件号码", "出生日期", "手机号码", "电子邮箱"};
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("用户信息表");
        sheet.setDefaultColumnWidth(20);
        //设置单元格下拉框
//        String[] sex = {"男", "女"};
//        String[] identity = {"居民身份证", "士官证", "学生证", "驾驶证", "护照", "港澳通行证"};
//        // ==> start
//        // 设置多少列为下拉框并给赋值
//        createDropDownList(sheet,sex,0,10000,2,2);
//        createDropDownList(sheet,identity,0,10000,3,3);

        // 样式
        HSSFCellStyle headStyle = workbook.createCellStyle();
        headStyle.setFillForegroundColor((short) 70);
        headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        HSSFDataFormat dataFormat = workbook.createDataFormat();
        headStyle.setDataFormat(dataFormat.getFormat("@"));
        // 居中
        headStyle.setAlignment(HorizontalAlignment.CENTER);
        HSSFDataFormat format = workbook.createDataFormat();
        //设置单元格格式为常规
        headStyle.setDataFormat(format.getFormat("@"));
        //设置头格式(第一行)

        int rowNO = 0;
        HSSFRow row = null;
        HSSFCell cell = null;
        row = sheet.createRow(rowNO++);
        row.setHeight((short) 400);
        //两个for循环，生成execl的标题
        for (int i = 0; i < title.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(headStyle);
        }
        //excel文件名
        String fileName = "用户上传模板.xls";
        //告诉浏览器返回文件下载
        response.setContentType("application/download;charset=UTF-8");
        //激活文件下载保存框
        response.setHeader("Content-disposition", "attachment;filename=\""
                + new String(fileName.getBytes("UTF-8"), "ISO8859_1") + "\"");
        OutputStream out = response.getOutputStream();
        workbook.write(out);
        out.close();
        return null;
    }



    public static Map<String, Object> parseExcel1(String fileName,InputStream inputStream){
        Workbook workbook = null;
        Map<String, Object> returnMap = new HashMap<>();
        try {
            logger.info("开始解析Excel......");
            //判断并加载Excel文件
            workbook = getWorkbook(inputStream, fileName);

            // 返回数据

            int numberOfSheets = workbook.getNumberOfSheets();

            List<Sheet> sheetList = new ArrayList<>();
            // 遍历Excel中所有的sheet工作簿
            for (int i = 0; i < numberOfSheets; i++) {
                //根据id获取对应的工作簿
                Sheet sheet = workbook.getSheetAt(i);
                if (sheet == null) {
                    continue;
                }

                //获取工作簿的名称
                String sheetName = sheet.getSheetName();
                logger.info("当前excel中共有 [{}] 个工作簿，名称为：{}", sheet, sheetName);

                //总行数
                int totalRows = sheet.getPhysicalNumberOfRows();
                logger.info("当前工作簿，共计 [{}] 行", totalRows);
                sheetList.add(sheet);

                //获取标题栏行
                Row row = sheet.getRow(2);

                //总列列数
                int totalCells = 0;
                if (totalRows >= 1 && row != null) {
                    totalCells = row.getPhysicalNumberOfCells();
                } else {
                    throw new BindException("excel为空");
                }

                Map<Object, Object> keyMap = new HashMap<>();
                List<Integer> keys = new ArrayList<>();
                //第一行的数据
                for (int cellIndex = 0; cellIndex < totalCells; cellIndex++) {
                    Cell cell = row.getCell(cellIndex);
                    keyMap.put(cellIndex, cell.getStringCellValue());
                    keys.add(cellIndex);
                }
                //处理标题行、合并单元格
                dellMergeCell(sheet, row, totalCells, 2, keyMap);
                //将Excel转为Map
                List<Map<Object, Object>> excelToMapList = excelToMapList(sheet, totalRows, totalCells, keyMap);
                returnMap.put(sheetName, excelToMapList);
            }
            logger.info("解析Excel结束......");
            return returnMap;
        } catch (Exception e) {
            logger.info("解析Excel异常......",e);
            return null;
        }
    }
    /**
     * 解析Excel并转换为map
     *
     * @param file excel文件
     * @return 结果集
     * @throws IOException
     */
    public static Map<String, Object> parseExcel(MultipartFile file) {
        InputStream inputStream = null;
        Workbook workbook = null;
        Map<String, Object> returnMap = new HashMap<>();
        try {
            logger.info("开始解析Excel......");
            String fileName = file.getOriginalFilename();
            if (StringUtil.isBlank(fileName)) {
                return null;
            }
            inputStream = file.getInputStream();

            //判断并加载Excel文件
            workbook = getWorkbook(inputStream, fileName);

            // 返回数据

            int numberOfSheets = workbook.getNumberOfSheets();

            List<Sheet> sheetList = new ArrayList<>();
            // 遍历Excel中所有的sheet工作簿
            for (int i = 0; i < numberOfSheets; i++) {
                //根据id获取对应的工作簿
                Sheet sheet = workbook.getSheetAt(i);
                if (sheet == null) {
                    continue;
                }

                //获取工作簿的名称
                String sheetName = sheet.getSheetName();
                logger.info("当前excel中共有 [{}] 个工作簿，名称为：{}", sheet, sheetName);

                //总行数
                int totalRows = sheet.getPhysicalNumberOfRows();
                logger.info("当前工作簿，共计 [{}] 行", totalRows);
                sheetList.add(sheet);

                //获取标题栏行
                Row row = sheet.getRow(2);

                //总列列数
                int totalCells = 0;
                if (totalRows >= 1 && row != null) {
                    totalCells = row.getPhysicalNumberOfCells();
                } else {
                    throw new BindException("excel为空");
                }

                Map<Object, Object> keyMap = new HashMap<>();
                List<Integer> keys = new ArrayList<>();
                //第一行的数据
                for (int cellIndex = 0; cellIndex < totalCells; cellIndex++) {
                    Cell cell = row.getCell(cellIndex);
                    keyMap.put(cellIndex, cell.getStringCellValue());
                    keys.add(cellIndex);
                }
                //处理标题行、合并单元格
                dellMergeCell(sheet, row, totalCells, 2, keyMap);
                //将Excel转为Map
                List<Map<Object, Object>> excelToMapList = excelToMapList(sheet, totalRows, totalCells, keyMap);
                returnMap.put(sheetName, excelToMapList);
            }
            logger.info("解析Excel结束......");
            return returnMap;
        } catch (Exception e) {
            logger.info("解析Excel异常......",e);
            return null;
        }finally {
            try{
                assert inputStream != null;
                inputStream.close();
                workbook.close();
            }catch (Exception e){
                logger.info("文件流关闭异常......",e);
            }

        }
    }

    /**
     * 将excel数据转换为Map
     * @param sheet
     * @param totalRow
     * @param totalCells
     * @param keyMap
     * @return
     */
    private static List<Map<Object, Object>> excelToMapList(Sheet sheet, int totalRow, int totalCells, Map<Object, Object> keyMap) {
        logger.info("开始将Excel抓换为Map......");

        List<Map<Object, Object>> excelToMapList = new ArrayList<>();
        Map<Object, Object> excelToMap = new HashMap<>();
        Map<Object, Object> mergeCellMap = new HashMap<>();
        StringBuilder builder = new StringBuilder();

        for (int rowIndex = 0; rowIndex < totalRow; rowIndex++) {
            if (rowIndex == 2) {
                logger.info("第三行标题栏已解析，不在重复解析，标题栏数据为：[{}]", keyMap);
                continue;
            }
            Row row = sheet.getRow(rowIndex);
            boolean flag = dellMergeCell(sheet, row, totalCells, rowIndex, mergeCellMap);
            if (flag) {
                //将合并单元格内容放入excelToMap
                excelToMap.putAll(mergeCellMap);
                //清除合并单元格的临时数据
                mergeCellMap.clear();
            } else {
                if (row == null) {
                    continue;
                }
                for (int cellIndex = 0; cellIndex < totalCells; cellIndex++) {
                    Cell cell = row.getCell(cellIndex);
                    builder.append(formatCellValue(cell)).append(".").append(keyMap.get(cellIndex)).append(":");
                }
            }
        }
        excelToMapList.add(excelToMap);
        excelToMapList.add(StringUtil.formatToMap(keyMap, builder.toString()));
        return excelToMapList;
    }

    /**
     * 处理合并单元格
     *
     * @param sheet      当前表单
     * @param row        行
     * @param totalCells 总列数
     * @param rowIndex   行号
     * @param hashMap    合并单元格处理结果
     * @return true /false
     */
    private static boolean dellMergeCell(Sheet sheet, Row row, int totalCells, int rowIndex, Map<Object, Object> hashMap) {
        String cellValue = null;
        boolean mergeRegion =false;
        boolean flag = false;
        Map<Integer, String> keyMap = new HashMap<>();
        for (int cellIndex = 0; cellIndex < totalCells; cellIndex++) {
            //判断是否有合并单元格
             mergeRegion = isMergedRegion(sheet, rowIndex, cellIndex);
            Cell cell = row.getCell(cellIndex);
            if (mergeRegion) {
                String value = formatCellValue(cell).toString();
                //已经是合并单元格，则只取一个单元格的值即可
                if (StringUtil.isNotBlank(value)) {
                    cellValue = value;
                    hashMap.put(keyMap.get(cellIndex - 1), cellValue);
                    //清空缓存
                    keyMap.clear();
                    flag = true;
                    break;
                }
            } else {
                String value = formatCellValue(cell).toString();
                if (StringUtil.isNotBlank(value)) {
                    keyMap.put(cellIndex, value);
                    flag =  false;
                }
            }
        }
        hashMap.putAll(keyMap);
        logger.info("合并单元格处理结束");
        return flag;
    }

    /**
     * 判断表格类型
     *
     * @param inputStream 文件输入流
     * @param fileName 文件名称
     * @return Workbook
     * @throws IOException
     */
    private static Workbook getWorkbook(InputStream inputStream, String fileName) throws IOException {
        Workbook workbook;
        if (fileName.endsWith(CommonConstant.EXCEL_XLS)) {
            workbook = new HSSFWorkbook(inputStream);
        } else {
            workbook = new XSSFWorkbook(inputStream);
        }
        return workbook;
    }


    /**
     * 判断当前行是否是合并单元格
     *
     * @param sheet
     * @param rowIndex 行下标
     * @param column   列下标
     * @return
     */
    private static boolean isMergedRegion(Sheet sheet, int rowIndex, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if(rowIndex == firstRow && rowIndex == lastRow){
                if(column >= firstColumn && column <= lastColumn){
                    return true;
                }
            }
        }
        return false;
    }

    /**
     * 对表格中单个单元格进行处理
     *
     * @param cell
     * @return
     */
    public static Object formatCellValue(Cell cell) {
        if (cell == null) {
            return " ";
        }
        Object value = null;
        // 格式化number String字符
        DecimalFormat df = new DecimalFormat("0");
        // 日期格式化
        SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd");
        // 格式化数字
        DecimalFormat df2 = new DecimalFormat("0");
        CellType cellType = cell.getCellType();
        switch (cellType) {
            case STRING:
                value = cell.getRichStringCellValue().getString();
                break;
            case NUMERIC:
                if ("General".equals(cell.getCellStyle().getDataFormatString())) {
                    value = df.format(cell.getNumericCellValue());
                } else if ("m/d/yy".equals(cell.getCellStyle().getDataFormatString())) {
                    value = sdf.format(cell.getDateCellValue());
                } else {
                    value = df2.format(cell.getNumericCellValue());
                }
                break;
            case BOOLEAN:
                value = cell.getBooleanCellValue();
                break;
            case BLANK:
                value = "";
                break;
            default:
                break;
        }
        return value;
    }
}
